import tkinter as tk
from tkinter import scrolledtext
import pymssql

def open(s):
    window = tk.Tk()
    window.title("养护人员-" + s + "---查询")
    window.geometry("400x300")
#设置列的权重
    window.grid_columnconfigure(0, weight=1)
    window.grid_columnconfigure(1, weight=2)
    window.grid_columnconfigure(2, weight=1)

    label1 = tk.Label(window, text="请输入要查询的任务编号或养护对象,")
    label1.grid(row=0, column=0)
    label2 = tk.Label(window, text="点击对应的按钮进行查询。")
    label2.grid(row=0, column=1)
    label_a = tk.Label(window, text="输入")
    label_a.grid(row=1, column=0)
    entry2 = tk.Entry(window)
    entry2.grid(row=1, column=1)

    conn = pymssql.connect(host='localhost', server='DESKTOP-NBNT8AG', port='1433', user='lxy',
                           password='lxy618',
                           database='植物管理系统', charset='utf8')

    if conn:
        print("连接成功！")

    def check1():
        message = entry2.get()

        cursor = conn.cursor()  # 获取游标
        sql = f"SELECT DISTINCT * FROM 养护 WHERE 养护.养护任务编号='{message}'"
        cursor.execute(sql)
        result = ""

        if cursor.rowcount == 0:
            result = "养护任务不存在！"
        else:
            for row in cursor:   #遍历输出
                result += "养护任务编号：" + "%s" % (row[0]) + "  " + "养护任务名称：" + "%s" % (row[1]) + "  " + "养护任务执行地点：" + "%s" % (row[2]) + "  " + "养护任务执行时间：" + "%s" % (row[3]) + "  " + "养护任务执行人员：" + "%s" % (row[4]) + "  " + "养护任务描述：" + "%s" % (row[5]) + "  " + "养护对象：" + "%s" % (row[6]) + "  " + "养护任务执行状态：" + "%s" % (row[7])

        scrollbar.insert(tk.INSERT, result)

    def check2():
        message = entry2.get()

        cursor = conn.cursor()  # 获取游标
        sql = f"SELECT DISTINCT * FROM 养护 WHERE 养护.养护对象='{message}'"
        cursor.execute(sql)
        result = ""

        if cursor.rowcount == 0:
            result = "养护任务不存在！"
        else:
            for row in cursor:  # 遍历输出
                result += "养护任务编号：" + "%s" % (row[0]) + "  " + "养护任务名称：" + "%s" % (
                row[1]) + "  " + "养护任务执行地点：" + "%s" % (row[2]) + "  " + "养护任务执行时间：" + "%s" % (
                          row[3]) + "  " + "养护任务执行人员：" + "%s" % (row[4]) + "  " + "养护任务描述：" + "%s" % (
                          row[5]) + "  " + "养护对象：" + "%s" % (row[6]) + "  " + "养护任务执行状态：" + "%s" % (row[7])

        scrollbar.insert(tk.INSERT, result)

    label = tk.Label(window, text="----请选择查询方式----")
    label.grid(row=2, column=0)

    button1 = tk.Button(window, text="按养护任务编号进行查询", command=lambda: check1(), width=20, height=2)
    button2 = tk.Button(window, text="按养护对象进行查询", command=lambda: check2(), width=20, height=2)

    return_button = tk.Button(window, text="返回", command=window.destroy, width=20, height=1)
    button1.grid(row=3, column=0)
    button2.grid(row=3, column=1)
    return_button.grid(row=4, column=1, sticky="nsew")

    scrollbar = scrolledtext.ScrolledText(window, wrap=tk.WORD, width=60, height=10)
    scrollbar.grid(row=5, column=0, columnspan=4, sticky="nsew")
    window.mainloop()
    pass